import tkinter as tk  
from tkinter import ttk  
from tkcalendar import DateEntry  
import pyodbc  
import pandas as pd  
from datetime import datetime  
from tkinter import messagebox  

class SQLDataViewer:  
    def __init__(self, root):  
        self.root = root  
        self.root.title("SQL Server Data Viewer")  
        
        # 获取屏幕尺寸  
        screen_width = root.winfo_screenwidth()  
        screen_height = root.winfo_screenheight()  
        
        # 设置窗口尺寸  
        window_width = 1330  
        window_height = 800  
        
        # 计算窗口位置使其居中  
        x = (screen_width - window_width) // 2  
        y = (screen_height - window_height) // 2  
        
        # 设置窗口大小和位置  
        self.root.geometry(f"{window_width}x{window_height}+{x}+{y}")  

        # 数据库连接配置  
        self.connection_string = (  
            "DRIVER={SQL Server};"  
            "SERVER=localhost;"  
            "DATABASE=NEUBG;"  
            "Trusted_Connection=yes;"  
        )  

        # 创建主框架  
        self.main_frame = ttk.Frame(root, padding="10")  
        self.main_frame.pack(fill=tk.BOTH, expand=True)  

        # 创建按钮框架  
        self.button_frame = ttk.Frame(self.main_frame)  
        self.button_frame.pack(fill=tk.X, pady=(0, 10))  

        # 创建日期选择器  
        self.start_date_label = ttk.Label(self.button_frame, text="开始日期:")  
        self.start_date_label.pack(side=tk.LEFT, padx=5)  
        self.start_date = DateEntry(self.button_frame, width=12, background='darkblue', foreground='white', borderwidth=2)  
        self.start_date.pack(side=tk.LEFT, padx=5)  

        self.end_date_label = ttk.Label(self.button_frame, text="结束日期:")  
        self.end_date_label.pack(side=tk.LEFT, padx=5)  
        self.end_date = DateEntry(self.button_frame, width=12, background='darkblue', foreground='white', borderwidth=2)  
        self.end_date.pack(side=tk.LEFT, padx=5)  

        # 创建文件名选择  
        self.filename_label = ttk.Label(self.button_frame, text="文件名:")  
        self.filename_label.pack(side=tk.LEFT, padx=5)  
        self.filename_var = tk.StringVar()  
        self.filename_combo = ttk.Entry(self.button_frame, textvariable=self.filename_var, width=30)  
        self.filename_combo.pack(side=tk.LEFT, padx=5)  
        # 添加选择按钮  
        self.select_button = ttk.Button(self.button_frame, text="选择", command=self.update_filename_options)  
        self.select_button.pack(side=tk.LEFT, padx=2)  

        # 创建试样号下拉框  
        self.sample_label = ttk.Label(self.button_frame, text="试样号:")  
        self.sample_label.pack(side=tk.LEFT, padx=5)  
        self.sample_var = tk.StringVar()  
        self.sample_combo = ttk.Entry(self.button_frame, textvariable=self.sample_var, width=30)  
        self.sample_combo.pack(side=tk.LEFT, padx=5)  
        # 添加选择按钮  
        self.sample_select_button = ttk.Button(self.button_frame, text="选择", command=self.update_sample_options)  
        self.sample_select_button.pack(side=tk.LEFT, padx=2)   

        # 创建查询按钮  
        self.query_button = ttk.Button(self.button_frame, text="查询数据", command=self.query_data)  
        self.query_button.pack(side=tk.LEFT, padx=5)  

        # 创建测试按钮  
        self.test_button = ttk.Button(self.button_frame, text="测试查询", command=self.test_query)  
        self.test_button.pack(side=tk.LEFT, padx=5)  

        # 创建表格  
        self.create_table()  

        # 绑定事件  
        self.start_date.bind("<<DateSelected>>", lambda e: self.on_date_change())  
        self.end_date.bind("<<DateSelected>>", lambda e: self.on_date_change())  

    def connect_to_database(self):  
        try:  
            return pyodbc.connect(self.connection_string)  
        except Exception as e:  
            messagebox.showerror("连接错误", f"无法连接到数据库: {str(e)}")  
            return None  

    def on_date_change(self):  
        print("日期改变事件触发")  
        self.filename_var.set('')  
        self.sample_var.set('')  

    def update_filename_options(self, event=None):  
        """更新文件名选择并处理多选"""  
        # 创建选择窗口  
        select_window = tk.Toplevel(self.root)  
        select_window.title("选择文件名")  
        
        # 获取主窗口位置并设置弹窗位置  
        main_x = self.root.winfo_x()  
        main_y = self.root.winfo_y()  
        select_window.geometry(f"300x400+{main_x+400}+{main_y+100}")  
        
        # 创建框架和列表框  
        frame = ttk.Frame(select_window, padding="10")  
        frame.pack(fill=tk.BOTH, expand=True)  
        
        scrollbar = ttk.Scrollbar(frame)  
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)  
        
        listbox = tk.Listbox(frame, selectmode=tk.MULTIPLE, yscrollcommand=scrollbar.set)  
        listbox.pack(fill=tk.BOTH, expand=True)  
        
        scrollbar.config(command=listbox.yview)  
        
        try:  
            conn = self.connect_to_database()  
            if conn:  
                cursor = conn.cursor()  
                start_date = self.start_date.get_date().strftime('%Y-%m-%d')  
                end_date = self.end_date.get_date().strftime('%Y-%m-%d')  
                
                query = """  
                SELECT DISTINCT excel_name  
                FROM dbo.TXEX2  
                WHERE CAST(DT AS DATE) >= ?  
                AND CAST(DT AS DATE) <= ?  
                ORDER BY excel_name ASC  
                """  
                cursor.execute(query, (start_date, end_date))  
                filenames = [row[0] for row in cursor.fetchall()]  
                
                # 添加文件名到列表框  
                for filename in filenames:  
                    listbox.insert(tk.END, filename)  
                
                conn.close()  
                
                def confirm():  
                    # 获取选中的文件名  
                    selected_indices = listbox.curselection()  
                    selected_files = [listbox.get(i) for i in selected_indices]  
                    
                    # 更新显示  
                    self.filename_var.set('; '.join(selected_files))  
                    
                    # 清空试样号选择  
                    self.sample_var.set('')  
                    
                    # 关闭选择窗口  
                    select_window.destroy()  
                    
                    # 自动触发试样号更新  
                    self.update_sample_options()  
                
                # 添加按钮  
                button_frame = ttk.Frame(select_window)  
                button_frame.pack(fill=tk.X, pady=5)  
                
                ttk.Button(button_frame, text="确定", command=confirm).pack(side=tk.LEFT, padx=5)  
                ttk.Button(button_frame, text="取消", command=select_window.destroy).pack(side=tk.LEFT)  
                
        except Exception as e:  
            print(f"错误详情: {str(e)}")  
            messagebox.showerror("错误", f"更新文件名失败: {str(e)}")  
            select_window.destroy()  

    def update_sample_options(self):  
        """更新试样号选择并处理多选"""  
        # 如果没有选择文件名，提示并返回  
        if not self.filename_var.get():  
            messagebox.showinfo("提示", "请先选择文件名")  
            return  
            
        # 创建选择窗口  
        select_window = tk.Toplevel(self.root)  
        select_window.title("选择试样号")  
        
        # 获取主窗口位置并设置弹窗位置  
        main_x = self.root.winfo_x()  
        main_y = self.root.winfo_y()  
        select_window.geometry(f"300x400+{main_x+750}+{main_y+100}")  
        
        # 创建框架和列表框  
        frame = ttk.Frame(select_window, padding="10")  
        frame.pack(fill=tk.BOTH, expand=True)  
        
        scrollbar = ttk.Scrollbar(frame)  
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)  
        
        listbox = tk.Listbox(frame, selectmode=tk.MULTIPLE, yscrollcommand=scrollbar.set)  
        listbox.pack(fill=tk.BOTH, expand=True)  
        
        scrollbar.config(command=listbox.yview)  
        
        try:  
            conn = self.connect_to_database()  
            if conn:  
                cursor = conn.cursor()  
                start_date = self.start_date.get_date().strftime('%Y-%m-%d')  
                end_date = self.end_date.get_date().strftime('%Y-%m-%d')  
                selected_files = self.filename_var.get().split('; ')  
                
                # 构建查询条件  
                filename_conditions = ' OR '.join(['excel_name = ?' for _ in selected_files])  
                query = f"""  
                SELECT DISTINCT section  
                FROM dbo.TXEX2  
                WHERE CAST(DT AS DATE) >= ?  
                AND CAST(DT AS DATE) <= ?  
                AND ({filename_conditions})  
                ORDER BY section ASC  
                """  
                
                # 执行查询  
                params = [start_date, end_date] + selected_files  
                cursor.execute(query, params)  
                sample_ids = [row[0] for row in cursor.fetchall()]  
                
                # 添加试样号到列表框  
                for sample_id in sample_ids:  
                    listbox.insert(tk.END, sample_id)  
                
                conn.close()  
                
                def confirm():  
                    # 获取选中的试样号  
                    selected_indices = listbox.curselection()  
                    selected_samples = [listbox.get(i) for i in selected_indices]  
                    
                    # 更新显示  
                    self.sample_var.set('; '.join(selected_samples))  
                    
                    # 关闭选择窗口  
                    select_window.destroy()  
                
                # 添加按钮  
                button_frame = ttk.Frame(select_window)  
                button_frame.pack(fill=tk.X, pady=5)  
                
                ttk.Button(button_frame, text="确定", command=confirm).pack(side=tk.LEFT, padx=5)  
                ttk.Button(button_frame, text="取消", command=select_window.destroy).pack(side=tk.LEFT)  
                
        except Exception as e:  
            print(f"错误详情: {str(e)}")  
            messagebox.showerror("错误", f"更新试样号失败: {str(e)}")  
            select_window.destroy() 

    def test_query(self):  
        try:  
            conn = self.connect_to_database()  
            if conn:  
                cursor = conn.cursor()  
                start_date = self.start_date.get_date().strftime('%Y-%m-%d')  
                end_date = self.end_date.get_date().strftime('%Y-%m-%d')  
                
                query = """  
                SELECT DT, excel_name, section  
                FROM dbo.TXEX2  
                WHERE CAST(DT AS DATE) >= ?  
                AND CAST(DT AS DATE) <= ?  
                ORDER BY DT ASC  
                """  
                
                cursor.execute(query, (start_date, end_date))  
                results = cursor.fetchall()  
                
                if results:  
                    message = f"在{start_date}到{end_date}期间找到的数据：\n"  
                    for row in results[:5]:  # 只显示前5条  
                        message += f"时间：{row[0]}, 文件名：{row[1]}, 试样号：{row[2]}\n"  
                    message += f"\n总共找到{len(results)}条记录"  
                else:  
                    message = f"在{start_date}到{end_date}期间没有找到数据"  
                
                messagebox.showinfo("查询结果", message)  
                conn.close()  
        except Exception as e:  
            messagebox.showerror("错误", f"测试查询失败: {str(e)}")  

    def create_table(self):  
        # 创建表格框架  
        self.table_frame = ttk.Frame(self.main_frame)  
        self.table_frame.pack(fill=tk.BOTH, expand=True)  

        # 创建并配置样式  
        style = ttk.Style()  
        style.configure("Treeview", rowheight=25)  # 设置行高  
        style.configure("Treeview.Heading", anchor="center")  # 表头居中  
        
        # 创建Treeview  
        self.tree = ttk.Treeview(self.table_frame, show='headings', style="Treeview")  
        
        # 添加滚动条  
        vsb = ttk.Scrollbar(self.table_frame, orient="vertical", command=self.tree.yview)  
        hsb = ttk.Scrollbar(self.table_frame, orient="horizontal", command=self.tree.xview)  
        self.tree.configure(yscrollcommand=vsb.set, xscrollcommand=hsb.set)  

        # 布局  
        self.tree.grid(column=0, row=0, sticky='nsew')  
        vsb.grid(column=1, row=0, sticky='ns')  
        hsb.grid(column=0, row=1, sticky='ew')  

        self.table_frame.grid_columnconfigure(0, weight=1)  
        self.table_frame.grid_rowconfigure(0, weight=1)  

    def query_data(self):  
        try:  
            # 检查是否选择了文件名和试样号  
            if not self.filename_var.get():  
                messagebox.showinfo("提示", "请选择文件名")  
                return  
            if not self.sample_var.get():  
                messagebox.showinfo("提示", "请选择试样号")  
                return  

            conn = self.connect_to_database()  
            if conn:  
                cursor = conn.cursor()  
                start_date = self.start_date.get_date().strftime('%Y-%m-%d')  
                end_date = self.end_date.get_date().strftime('%Y-%m-%d')  
                selected_files = self.filename_var.get().split('; ')  
                selected_samples = self.sample_var.get().split('; ')  

                # 构建文件名和试样号的条件  
                filename_conditions = ' OR '.join(['excel_name = ?' for _ in selected_files])  
                sample_conditions = ' OR '.join(['section = ?' for _ in selected_samples])  
                
                query = f"""  
                SELECT * FROM dbo.TXEX2  
                WHERE CAST(DT AS DATE) >= ?  
                AND CAST(DT AS DATE) <= ?  
                AND ({filename_conditions})  
                AND ({sample_conditions})  
                ORDER BY DT ASC, SNUMB ASC  
                """  
                
                # 合并所有参数  
                params = [start_date, end_date] + selected_files + selected_samples  
                cursor.execute(query, params)  
                
                # 获取列名  
                columns = [column[0] for column in cursor.description]  
                
                # 清除现有数据  
                for item in self.tree.get_children():  
                    self.tree.delete(item)  
                
                # 设置列  
                self.tree['columns'] = columns  
                
                # 设置列标题和宽度  
                for col in columns:  
                    self.tree.heading(col, text=col, anchor='center')  
                    # 根据列类型设置宽度  
                    if col == 'DT':  
                        width = 150  # 时间列  
                    elif col in ['excel_name']:  
                        width = 150  # 文件名列  
                    elif col == 'section':  
                        width = 100  # 试样号列  
                    elif col in ['inclusion_type']:  
                        width = 150  # 备注类列  
                    else:  
                        width = 100  # 其他数值列  
                    
                    self.tree.column(col, width=width, minwidth=width, anchor='center')  

                # 获取并处理数据  
                rows = cursor.fetchall()  
                formatted_rows = []  

                # 定义列类型  
                int_columns = ['SNUMB', 'IDNO', 'FOV']  
                varchar_columns = ['excel_name', 'section', 'inclusion_type']  

                for row in rows:  
                    formatted_row = []  
                    for i, value in enumerate(row):  
                        col_name = columns[i]  
                        
                        if value is None:  
                            formatted_value = ""  
                        elif col_name == 'DT':  
                            # 时间列，截取到分钟  
                            formatted_value = str(value)[:16]  
                        elif col_name in int_columns:  
                            # 整数列  
                            formatted_value = str(int(value)) if value else "0"  
                        elif col_name in varchar_columns:  
                            # 字符串列  
                            formatted_value = str(value)  
                        else:  
                            # 其他所有数值列当作浮点数处理，保留10位小数  
                            if value is None or value == 'NULL':  # 同时处理None和'NULL'字符串  
                                formatted_value = "0.00"  
                            elif value == 0:  
                                formatted_value = "0"  
                            else:  
                                try:  
                                    formatted_value = f"{float(value):.10f}"  
                                except:  
                                    formatted_value = str(value) 
                        
                        formatted_row.append(formatted_value)  
                    
                    formatted_rows.append(formatted_row)  

                # 插入格式化后的数据  
                for row in formatted_rows:  
                    self.tree.insert('', 'end', values=row)  

                # 显示查询结果统计  
                result_count = len(rows)  
                if result_count > 0:  
                    messagebox.showinfo("查询完成", f"共找到 {result_count} 条记录")  
                else:  
                    messagebox.showinfo("提示", "没有找到匹配的数据")  

                conn.close()  

        except Exception as e:  
            print(f"错误详情: {str(e)}")  
            messagebox.showerror("错误", f"查询数据失败: {str(e)}")  

def main():  
    root = tk.Tk()  
    app = SQLDataViewer(root)  
    root.mainloop()  

if __name__ == "__main__":  
    main()